import pandas as pd
import numpy as np
from initdatabase import *
from interactdb import *
from datetime import datetime, timedelta
import openpyxl
from dateutil.relativedelta import relativedelta
import math
from common_func_defs import *
import sys

####################################################### 加工方法定义 ########################################################
'''—————————————————————————————方法：与时间相关的方法—————————————————————————————'''
def f_getbeginenddate(monthinput): #'2023-08'
    promotion_split_pop_base_month = datetime.strptime(monthinput, '%Y-%m')
    begindate2 = promotion_split_pop_base_month.replace(day=1)
    enddate2 = (promotion_split_pop_base_month.replace(day=1) + timedelta(days=32)).replace(day=1) - timedelta(days=1)
    return [begindate2,enddate2]

def f_getpremonthofadate(datestr):
    given_date = datetime.strptime(datestr, "%Y-%m-%d")
    first_day_of_given_month = given_date.replace(day=1)
    one_day = timedelta(days=1)
    last_day_of_previous_month = first_day_of_given_month - one_day
    previous_month = last_day_of_previous_month.strftime("%Y-%m")
    return previous_month

def f_getsomemonthofamonth( months_back, years_back,  input_month):
    current_date = datetime.strptime(input_month, "%Y-%m")
    previous_date = current_date - relativedelta(months=months_back, years=years_back)
    return previous_date.strftime("%Y-%m")


'''—————————————————————————————方法：检查用户希望预测的内容是否缺失支撑预测的参考数据，并为预测方法准备好参考数据—————————————————————————————'''
def pre_predict_check(dffront):
    info=''
    returndict={}
    #检查dffront：
    #predict_obj是下拉选项（选项有“大促”和“月”）不可能为空；
    #predict_obj选择大促时，predict_month为nan；predict_big_promo_year和predict_big_promo_name是选项；ref_big_promo_year、ref_big_promo_name、act_pop_pred_ref_month、sto_promo_pred_ref_month、act_promo_pred_ref_month要么是选项要么是''，所以不会错误；
    #predict_obj选择月时，predict_big_promo_year、predict_big_promo_name、ref_big_promo_year、ref_big_promo_name为nan；predict_month是月份选项；act_pop_pred_ref_month、sto_promo_pred_ref_month、act_promo_pred_ref_month要么是选项要么是''，所以不会错误；
    #对数值字段进行检查：
    for col in ['est_trans_amount_goal','est_avg_order_price','benchmark_multiple','sto_est_fee_z','sto_est_fee_y','sto_est_fee_w','sto_est_fee_p','act_est_fee_z','act_est_fee_y','act_est_fee_w','act_est_fee_p']:
        try:
            dffront[col] = dffront[col].astype(float)
        except:
            raise ValueError("前端传入的需要用于数值计算的字段中存在非可计算数值字段")
    for col in ['avg_cost_multiple_sto','avg_cost_multiple_act','overlap_multiple_sto','overlap_multiple_act','paid_exppop_prop_multiple_sto','paid_exppop_prop_multiple_act']:
        if 'sto' in col:
            if len(dffront[col].iloc[0])!=6:
                raise ValueError("前端传入的数值list长度不符合要求")
        if 'act' in col:
            if len(dffront[col].iloc[0])!=3:
                raise ValueError("前端传入的数值list长度不符合要求")
        try:
            dffront.at[0, col] = [float(x) for x in dffront[col].iloc[0]]
        except:
            raise ValueError("前端传入的需要用于数值计算的数值list字段中存在非可计算数值字段")

    sto_pop_pred_ref_varlist=['his_A_interact_pop_sto','his_A_nointeract_pop_sto','his_I_interact_pop_sto','his_I_nointeract_pop_sto','his_PL_interact_pop_sto','his_PL_nointeract_pop_sto',
                              'his_A_interact_purpop_sto','his_A_nointeract_purpop_sto','his_I_interact_purpop_sto','his_I_nointeract_purpop_sto','his_PL_interact_purpop_sto','his_PL_nointeract_purpop_sto']
    act_pop_pred_ref_varlist=['inc_A_pop_act','inc_I_pop_act','inc_PL_pop_act',
                               'inc_A_purpop_act','inc_I_purpop_act','inc_PL_purpop_act']
    sto_promo_pred_ref_varlist=['his_A_interact_pop_sto','his_A_nointeract_pop_sto','his_I_interact_pop_sto','his_I_nointeract_pop_sto','his_PL_interact_pop_sto','his_PL_nointeract_pop_sto',
                                 'his_A_interact_z_exppop_sto','his_A_nointeract_z_exppop_sto',
                                 'his_A_interact_y_exppop_sto','his_A_nointeract_y_exppop_sto',
                                 'his_A_interact_w_exppop_sto','his_A_nointeract_w_exppop_sto',
                                 'his_A_interact_p_exppop_sto','his_A_nointeract_p_exppop_sto',
                                 'his_A_interact_paid_exppop_sto','his_A_nointeract_paid_exppop_sto',
                                 'his_I_interact_z_exppop_sto','his_I_nointeract_z_exppop_sto',
                                 'his_I_interact_y_exppop_sto','his_I_nointeract_y_exppop_sto',
                                 'his_I_interact_w_exppop_sto','his_I_nointeract_w_exppop_sto',
                                 'his_I_interact_p_exppop_sto','his_I_nointeract_p_exppop_sto',
                                 'his_I_interact_paid_exppop_sto','his_I_nointeract_paid_exppop_sto',
                                 'his_PL_interact_z_exppop_sto','his_PL_nointeract_z_exppop_sto',
                                 'his_PL_interact_y_exppop_sto','his_PL_nointeract_y_exppop_sto',
                                 'his_PL_interact_w_exppop_sto','his_PL_nointeract_w_exppop_sto',
                                 'his_PL_interact_p_exppop_sto','his_PL_nointeract_p_exppop_sto',
                                 'his_PL_interact_paid_exppop_sto','his_PL_nointeract_paid_exppop_sto',
                                 'paid_type','fee_z','fee_y','fee_w','fee_p'  #大促表中蓄水期的'paid_type_sto','fee_z_sto','fee_y_sto','fee_w_sto','fee_p_sto' 是用不上的（因为即使是预测大促蓄水期，也用的是蓄水期所在月的前一个月的数据作为参考数据）
                                ]
    act_promo_pred_ref_varlist=['inc_A_pop_act','inc_I_pop_act','inc_PL_pop_act',
                                'inc_A_z_exppop_act','inc_A_y_exppop_act','inc_A_w_exppop_act','inc_A_p_exppop_act','inc_A_paid_exppop_act',
                                'inc_I_z_exppop_act','inc_I_y_exppop_act','inc_I_w_exppop_act','inc_I_p_exppop_act','inc_I_paid_exppop_act',
                                'inc_PL_z_exppop_act','inc_PL_y_exppop_act','inc_PL_w_exppop_act','inc_PL_p_exppop_act','inc_PL_paid_exppop_act',
                                'paid_type', 'fee_z', 'fee_y', 'fee_w', 'fee_p'
                                ]
    #获取或上传数据时重点关注这些varlist中字段，purpop字段不能为空-----

    ##pre_pop_predict_check
    dffront = dffront.to_dict(orient='records')[0]
    act_pop_pred_ref_month = dffront['act_pop_pred_ref_month'] #无法用大促历史数据预测时才退而求其次用月历史数据
    sto_pop_pred_ref_month='' if act_pop_pred_ref_month=='' else f_getsomemonthofamonth(1,0,act_pop_pred_ref_month) #无法用大促历史数据预测时才退而求其次用月历史数据
    sto_promo_pred_ref_month = dffront['sto_promo_pred_ref_month'] #可修改蓄水期推广预测参考月，无输入值，则使用默认值
    act_promo_pred_ref_month = dffront['act_promo_pred_ref_month'] #无法用大促历史数据预测时才退而求其次用月历史数据
    ref_big_promo_year = dffront['ref_big_promo_year'] #可修改人群和推广预测参考大促，无输入值，则使用默认值
    ref_big_promo_name = dffront['ref_big_promo_name'] #可修改人群和推广预测参考大促，无输入值，则使用默认值

    if dffront['predict_obj']=='大促':
        predict_big_promo_year=dffront['predict_big_promo_year']
        predict_big_promo_name=dffront['predict_big_promo_name']
        # 得到ref_big_promo_year、ref_big_promo_name的默认值
        ref_big_promo_year_def =int(predict_big_promo_year)-1
        ref_big_promo_name_def =predict_big_promo_name
        # 若ref_big_promo_year和ref_big_promo_name有一个无输入值，就得用默认值
        if ref_big_promo_year=='' or ref_big_promo_name=='':
            ref_big_promo_year=ref_big_promo_year_def
            ref_big_promo_name=ref_big_promo_name_def
        #得到sto_promo_pred_ref_month的默认值
        #取数big_promotion_duration 大促区间表
        t = big_promotion_duration
        session = get_session()
        query = session.query(t.big_promotion_begin_date, t.big_promotion_end_date, t.big_promotion_sto_begin_date,
                              t.big_promotion_sto_end_date).filter(
            and_(t.big_promotion_year == dffront['predict_big_promo_year'],
                 t.big_promotion_name == dffront['predict_big_promo_name']
                 )
        )
        df = pd.read_sql(query.statement, session.bind)
        if df.shape[0] != 1:
            raise ValueError("大促区间表中无对应预测大促的起止日期信息")
        pred_sto_dur = df['big_promotion_sto_begin_date'].iloc[0].strftime('%Y-%m-%d') + '~' + df['big_promotion_sto_end_date'].iloc[0].strftime('%Y-%m-%d')
        pred_act_dur = df['big_promotion_begin_date'].iloc[0].strftime('%Y-%m-%d') + '~' + df['big_promotion_end_date'].iloc[0].strftime('%Y-%m-%d')
        sto_promo_pred_ref_month_def = f_getpremonthofadate(df['big_promotion_sto_begin_date'].iloc[0].strftime('%Y-%m-%d'))
        #若sto_promo_pred_ref_month无输入值，则使用默认值
        sto_promo_pred_ref_month=sto_promo_pred_ref_month or sto_promo_pred_ref_month_def
        #取数pop_purpop_exppop_month  人群购买人群曝光人群月表
        t = pop_purpop_exppop_month
        query = session.query(t).filter(
            and_(t.stat_time == sto_promo_pred_ref_month)
        )
        sto_promo_pred_ref_data = pd.read_sql(query.statement, session.bind)
        if sto_promo_pred_ref_data.shape[0]==0:
            raise ValueError("蓄水期推广预测参考月没有留存数据,参考数据不足")
        else:
            sto_promo_pred_ref_data= sto_promo_pred_ref_data[sto_promo_pred_ref_varlist]
            promo_base_sto_dur=sto_promo_pred_ref_month

        # 取数pop_purpop_exppop_bigpromo  人群购买人群曝光人群大促表
        t = pop_purpop_exppop_bigpromo
        query = session.query(t).filter(
            and_(t.big_promo_year ==ref_big_promo_year,t.big_promo_name==ref_big_promo_name)
        )
        sto_act_pop_promo_pred_ref_promo_data=pd.read_sql(query.statement, session.bind)
        if sto_act_pop_promo_pred_ref_promo_data.shape[0] ==0 :
            if not (sto_pop_pred_ref_month!='' and act_pop_pred_ref_month!='' and act_promo_pred_ref_month!=''):
                raise ValueError("参考大促没有留存数据的同时未给全用于预测的替代月,参考数据不足")
            else:
                templist=[]
                for item1 in [sto_pop_pred_ref_month,act_pop_pred_ref_month,act_promo_pred_ref_month]:
                    t = pop_purpop_exppop_month
                    query = session.query(t).filter(
                        and_(t.stat_time == item1)
                    )
                    tempdf=pd.read_sql(query.statement, session.bind)
                    if tempdf.shape[0]==0:
                        raise ValueError("参考大促退而求其次的参考月没有留存数据,参考数据不足")
                    else:
                        templist.append(tempdf)
                promo_base_act_dur = act_promo_pred_ref_month
                pop_base_sto_dur = sto_pop_pred_ref_month
                pop_base_act_dur = act_pop_pred_ref_month
                sto_pop_pred_ref_data=templist[0][sto_pop_pred_ref_varlist]
                act_pop_pred_ref_data=templist[1][act_pop_pred_ref_varlist]
                act_promo_pred_ref_data=templist[2][act_promo_pred_ref_varlist]
                purpop=templist[1]['purpop'].iloc[0]
        else:
            promo_base_act_dur=sto_act_pop_promo_pred_ref_promo_data['act_begindate'].iloc[0]+"~"+sto_act_pop_promo_pred_ref_promo_data['act_enddate'].iloc[0]
            pop_base_sto_dur=sto_act_pop_promo_pred_ref_promo_data['sto_begindate'].iloc[0]+"~"+sto_act_pop_promo_pred_ref_promo_data['sto_enddate'].iloc[0]
            pop_base_act_dur=sto_act_pop_promo_pred_ref_promo_data['act_begindate'].iloc[0]+"~"+sto_act_pop_promo_pred_ref_promo_data['act_enddate'].iloc[0]

            sto_pop_pred_ref_data= sto_act_pop_promo_pred_ref_promo_data[sto_pop_pred_ref_varlist]
            act_pop_pred_ref_data=sto_act_pop_promo_pred_ref_promo_data[act_pop_pred_ref_varlist]
            act_promo_pred_ref_data =sto_act_pop_promo_pred_ref_promo_data[act_promo_pred_ref_varlist]
            purpop=sto_act_pop_promo_pred_ref_promo_data['purpop'].iloc[0]

        t = pop_purpop_exppop_bigpromo
        query = session.query(t).filter(
            and_(t.big_promo_year ==predict_big_promo_year,t.big_promo_name==predict_big_promo_name)
        )
        sto_first_day_list = pd.read_sql(query.statement, session.bind)
        if sto_first_day_list.shape[0]==0:
            raise ValueError("所预测的大促的蓄水期第一天无数据")
        else:
            sto_first_day_list=sto_first_day_list[['his_A_interact_pop_stofirstday','his_A_nointeract_pop_stofirstday',
                             'his_I_interact_pop_stofirstday','his_I_nointeract_pop_stofirstday',
                             'his_PL_interact_pop_stofirstday','his_PL_nointeract_pop_stofirstday']].iloc[0].tolist() #[5146, 13778545, 8659, 2290503, 15812, 7382525]
        session.close()
    else:
        #预测粒度是月
        predict_month=dffront['predict_month']
        pred_sto_dur=predict_month
        pred_act_dur=f_getsomemonthofamonth(1,0,predict_month)
        #得到sto_pop_pred_ref_month、act_pop_pred_ref_month、sto_promo_pred_ref_month、act_promo_pred_ref_month的默认值
        sto_pop_pred_ref_month_def =f_getsomemonthofamonth(1,1,predict_month) #先month再year
        act_pop_pred_ref_month_def =f_getsomemonthofamonth(0,1,predict_month)
        sto_promo_pred_ref_month_def =f_getsomemonthofamonth(2,0,predict_month)
        act_promo_pred_ref_month_def =f_getsomemonthofamonth(0,1,predict_month)
        sto_pop_pred_ref_month =sto_pop_pred_ref_month or sto_pop_pred_ref_month_def
        act_pop_pred_ref_month =act_pop_pred_ref_month or act_pop_pred_ref_month_def
        sto_promo_pred_ref_month =sto_promo_pred_ref_month or sto_promo_pred_ref_month_def
        act_promo_pred_ref_month =act_promo_pred_ref_month or act_promo_pred_ref_month_def
        promo_base_sto_dur=sto_promo_pred_ref_month
        promo_base_act_dur=act_promo_pred_ref_month
        pop_base_sto_dur=sto_pop_pred_ref_month
        pop_base_act_dur=act_pop_pred_ref_month

        templist = []
        session=get_session()
        for item1 in [sto_pop_pred_ref_month, act_pop_pred_ref_month, sto_promo_pred_ref_month,act_promo_pred_ref_month]:
            t = pop_purpop_exppop_month
            query = session.query(t).filter(
                and_(t.stat_time == item1) #t.month改t.stat_time
            )
            tempdf = pd.read_sql(query.statement, session.bind)
            if tempdf.shape[0] == 0:
                raise ValueError("参考月没有留存数据")
            else:
                templist.append(tempdf)
        sto_pop_pred_ref_data = templist[0][sto_pop_pred_ref_varlist]
        act_pop_pred_ref_data = templist[1][act_pop_pred_ref_varlist]
        sto_promo_pred_ref_data = templist[2][sto_promo_pred_ref_varlist]
        act_promo_pred_ref_data = templist[3][act_promo_pred_ref_varlist]
        purpop=templist[1]['purpop'].iloc[0]

        t = pop_purpop_exppop_month
        query = session.query(t).filter(
            and_(t.stat_time ==f_getsomemonthofamonth(1,0,predict_month)) #t.month改t.stat_time #predict_month的前一个月才是蓄水期
        )
        tempdf = pd.read_sql(query.statement, session.bind)
        if tempdf.shape[0] == 0:
            raise ValueError("预测月的蓄水期的第一天人群量级没有留存数据")
        sto_first_day_list = tempdf[['his_A_interact_pop_stofirstday','his_A_nointeract_pop_stofirstday',
                             'his_I_interact_pop_stofirstday','his_I_nointeract_pop_stofirstday',
                             'his_PL_interact_pop_stofirstday','his_PL_nointeract_pop_stofirstday']].iloc[0].tolist()
        session.close()

    ##pre_promo_predict_check
    # for temp in ['z','y','w','p']:
    #     if (dffront['sto_est_fee_'+temp] != 0 ) and (sto_promo_pred_ref_data['fee_'+temp].iloc[0]  == 0):
    #         # est_fee非值为0时，需要对其对应的推广渠道做预测；而历史fee值为0时，无各渠道的人均成本、各渠道曝光人群占付费曝光人群的比例的历史数据，则无法对该推广渠道的情况做预测
    #         print('蓄水期需要预测的'+temp+'推广渠道在参考期没有留存数据，可能由于参考期该推广渠道未使用或数据缺失,直接return')
    #         info = info  + '蓄水期需要预测的'+temp+'推广渠道在参考期没有留存数据，可能由于参考期该推广渠道未使用或数据缺失'
    #         # 参考数据不足，直接return
    #         return [info, returndict]
    #     if (dffront['act_est_fee_'+temp] != 0 ) and (act_promo_pred_ref_data['fee_'+temp].iloc[0]  == 0):
    #         # est_fee非值为0时，需要对其对应的推广渠道做预测；而历史fee值为0时，无各渠道的人均成本、各渠道曝光人群占付费曝光人群的比例的历史数据，则无法对该推广渠道的情况做预测
    #         print('活动期需要预测的'+temp+'推广渠道在参考期没有留存数据，可能由于参考期该推广渠道未使用或数据缺失,直接return')
    #         info = info  + '活动期需要预测的'+temp+'推广渠道在参考期没有留存数据，可能由于参考期该推广渠道未使用或数据缺失'
    #         # 参考数据不足，直接return
    #         return [info, returndict]
    ##修改：要对某推广渠道做预测，若改渠道无历史数据，也可以做预测，用其他渠道在费用缺口上的平均情况即可

    #对要返回的用于预估的数据进行检查：
    if any(x is None or (isinstance(x, float) and math.isnan(x)) or x=='' for x in [pred_sto_dur,pred_act_dur,promo_base_sto_dur,promo_base_act_dur,pop_base_sto_dur,pop_base_act_dur]):
        raise ValueError("预测期或预测参考期的起止日期未明确")
    if any(x is None or (isinstance(x, float) and math.isnan(x)) or x=='' for x in sto_pop_pred_ref_data.iloc[0].tolist()) or not purpop:
        raise ValueError("蓄水期人群预测参考期数据有缺失")#那就一定有某种历史人群无法计算人群购买转化率，购买占比等
    if any(x is None or (isinstance(x, float) and math.isnan(x)) or x=='' for x in act_pop_pred_ref_data.iloc[0].tolist()):
        raise ValueError("活动期人群预测参考期数据有缺失")# 那就一定有某种新增人群无法计算人群购买转化率，购买占比等
    if any(x is None or (isinstance(x, float) and math.isnan(x)) or x=='' for x in sto_first_day_list):
        raise ValueError("预测蓄水期的第一天数据有缺失")#虽然人群预测和推广预测还能顺利进行，但是无法计算缺口了，还是希望数据是齐的
    if any(x is None or (isinstance(x, float) and math.isnan(x)) or x=='' for x in sto_promo_pred_ref_data.iloc[0].tolist()) or not purpop:
        raise ValueError("蓄水期推广预测参考期数据有缺失")#某渠道未进行推广投放应该对应的费用和曝光人群为0，而非数据缺失
    if any(x is None or (isinstance(x, float) and math.isnan(x)) or x=='' for x in act_promo_pred_ref_data.iloc[0].tolist()):
        raise ValueError("活动期推广预测参考期数据有缺失")#某渠道未进行推广投放应该对应的费用和曝光人群为0，而非数据缺失

    returndict['pred_sto_dur']=pred_sto_dur
    returndict['pred_act_dur'] = pred_act_dur
    returndict['promo_base_sto_dur'] = promo_base_sto_dur
    returndict['promo_base_act_dur'] = promo_base_act_dur
    returndict['pop_base_sto_dur'] = pop_base_sto_dur
    returndict['pop_base_act_dur'] = pop_base_act_dur
    returndict['sto_pop_pred_ref_data'] = sto_pop_pred_ref_data
    returndict['act_pop_pred_ref_data'] = act_pop_pred_ref_data
    returndict['sto_promo_pred_ref_data'] = sto_promo_pred_ref_data
    returndict['act_promo_pred_ref_data'] = act_promo_pred_ref_data
    returndict['purpop'] = purpop
    returndict['sto_first_day_list'] = sto_first_day_list
    for temp in ['est_trans_amount_goal','est_avg_order_price','benchmark_multiple',
                 'sto_est_fee_z','sto_est_fee_y','sto_est_fee_w','sto_est_fee_p','avg_cost_multiple_sto','overlap_multiple_sto','paid_exppop_prop_multiple_sto',
                 'act_est_fee_z', 'act_est_fee_y', 'act_est_fee_w', 'act_est_fee_p', 'avg_cost_multiple_act','overlap_multiple_act','paid_exppop_prop_multiple_act']:
        returndict[temp]=dffront[temp]
    return [info,returndict]#即将dffront中的内容检查转化后形成returndict中的内容（完整地检查，需要保证两个预测方法中不会报错）


'''—————————————————————————————方法：定位结果表Excel模块中特定位置—————————————————————————————'''
def find_value_in_range(sheet, target_value, start_row, end_row, start_column, end_column):
    for row in range(start_row, end_row + 1):
        for column in range(start_column, end_column + 1):
            cell_value = sheet.cell(row=row, column=column).value
            if cell_value == target_value:
                return row, column
    return None


'''—————————————————————————————方法：特殊情况，此处直接处理好数据格式—————————————————————————————'''
def is_float(value):
    try:
        float(value)
        return True
    except ValueError:
        return False
def floatwithtwo(l): return [round(float(x), 2) if x != '' and is_float(x) else x for x in l]
def percentwithtwo(l): return ["{:.2%}".format(float(x)) if x != '' and is_float(x) else x for x in l]
def toint(l): return [round(float(x)) if x != '' and is_float(x) else x for x in l]


'''—————————————————————————————方法：人群预测（GTA预测）—————————————————————————————'''
def get_pop_pred_res(dict,templateloc, res_path): #dict:pre_predict_check方法return的return_dict
    return_dict={}
    sto_pop_pred_ref_data = dict['sto_pop_pred_ref_data']
    act_pop_pred_ref_data = dict['act_pop_pred_ref_data']
    purpop = dict['purpop']
    est_trans_amount_goal = dict['est_trans_amount_goal']
    est_avg_order_price = dict['est_avg_order_price']
    benchmark_multiple = dict['benchmark_multiple']
    pred_sto_dur=dict['pred_sto_dur']
    pred_act_dur = dict['pred_act_dur']
    pop_base_sto_dur = dict['pop_base_sto_dur']
    pop_base_act_dur =  dict['pop_base_act_dur']

    his_pop_list=sto_pop_pred_ref_data.loc[0,['his_A_interact_pop_sto','his_A_nointeract_pop_sto','his_I_interact_pop_sto','his_I_nointeract_pop_sto',
                                              'his_PL_interact_pop_sto','his_PL_nointeract_pop_sto']].tolist()+\
                 act_pop_pred_ref_data.loc[0,['inc_A_pop_act','inc_I_pop_act','inc_PL_pop_act']].tolist()
    his_purpop_list=sto_pop_pred_ref_data.loc[0,['his_A_interact_purpop_sto','his_A_nointeract_purpop_sto','his_I_interact_purpop_sto','his_I_nointeract_purpop_sto',
                                          'his_PL_interact_purpop_sto','his_PL_nointeract_purpop_sto']].tolist()+\
             act_pop_pred_ref_data.loc[0,['inc_A_purpop_act','inc_I_purpop_act','inc_PL_purpop_act']].tolist()
    conversion_rate_list = [a / b for a, b in zip(his_purpop_list, his_pop_list)]
    res_conversion_rate_list = [x * benchmark_multiple for x in conversion_rate_list]
    res_purpop_prop_list=[x /purpop  for x in his_purpop_list]
    res_purpop_list = [x *(est_trans_amount_goal/est_avg_order_price)  for x in res_purpop_prop_list]
    res_pop_list = [a / b for a, b in zip(res_purpop_list, res_conversion_rate_list)]
    res_trans_amount_list = [x * est_avg_order_price for x in res_purpop_list]

    for l in [res_pop_list,res_purpop_list,res_purpop_prop_list,res_trans_amount_list,his_pop_list,his_purpop_list]:
        total = sum(l)
        l.append(total)
    res_conversion_rate_list.append(res_purpop_list[-1]/res_pop_list[-1])
    conversion_rate_list.append(his_purpop_list[-1]/his_pop_list[-1])

    return_dict['res_pop_list']=res_pop_list
    return_dict['res_conversion_rate_list'] = res_conversion_rate_list

    # res_pop_list=toint(res_pop_list)
    # res_conversion_rate_list=percentwithtwo(res_conversion_rate_list)
    # res_purpop_list=toint(res_purpop_list)
    # res_purpop_prop_list=percentwithtwo(res_purpop_prop_list)
    # res_trans_amount_list=floatwithtwo(res_trans_amount_list)
    # conversion_rate_list=percentwithtwo(conversion_rate_list)
    res_list=[res_pop_list,res_conversion_rate_list,res_purpop_list,res_purpop_prop_list,res_trans_amount_list,his_pop_list,his_purpop_list,conversion_rate_list]

    #display
    workbook = openpyxl.load_workbook(templateloc)  # 打开Excel模板
    worksheet = workbook['popres']  # 选择工作表
    for i, j in zip(list(range(1, 5)), [pred_sto_dur,pred_act_dur,pop_base_sto_dur,pop_base_act_dur]):
        row, column = find_value_in_range(worksheet, target_value='V' + str(i),
                                          start_row=1, end_row=12, start_column=1, end_column=13)
        worksheet.cell(row=row, column=column, value=j)
    for m,n in zip(['E','F','G','H','I','K','L','M'],res_list):
        for i in range(len(n)):
            cell = m+str(i+3)  # 计算单元格位置（例如：A2, A3, A4...）
            worksheet[cell] = n[i]  # 将数据写入单元格
    workbook.save(res_path)    #预测结果存储路径

    return return_dict #人群预测需要返回什么结果给推广预测用


'''—————————————————————————————方法：推广预测（推广AIPL预测）—————————————————————————————'''
def get_promo_pred_res(dict,resdict,ins,templateloc,res_file_path):#dict:pre_predict_check方法return的return_dict;resdict:get_pop_pred_res方法return的return_dict;ins:'蓄水人群预估'/'新增人群预估'
    est_avg_order_price=dict['est_avg_order_price']
    if ins=='蓄水人群预估':
        est_fee_z,est_fee_y,est_fee_w,est_fee_p,avg_cost_multiple,overlap_multiple,paid_exppop_prop_multiple,pred_dur,promo_base_dur,sto_first_day_list,promo_pred_ref_data= \
            [dict.get(key) for key in ['sto_est_fee_z','sto_est_fee_y','sto_est_fee_w','sto_est_fee_p','avg_cost_multiple_sto','overlap_multiple_sto','paid_exppop_prop_multiple_sto',
                                       'pred_sto_dur','promo_base_sto_dur','sto_first_day_list','sto_promo_pred_ref_data']]
        res_pop_list = resdict['res_pop_list'][:6]
        res_conversion_rate_list = resdict['res_conversion_rate_list'][:6]
    if ins=='新增人群预估':  #还未测试
        est_fee_z, est_fee_y, est_fee_w, est_fee_p,avg_cost_multiple,overlap_multiple,paid_exppop_prop_multiple,pred_dur,promo_base_dur,promo_pred_ref_data= \
            [dict.get(key) for key in ['act_est_fee_z', 'act_est_fee_y', 'act_est_fee_w', 'act_est_fee_p', 'avg_cost_multiple_act','overlap_multiple_act','paid_exppop_prop_multiple_act',
                                       'pred_act_dur','promo_base_act_dur','act_promo_pred_ref_data']]
        res_pop_list = resdict['res_pop_list'][6:9]
        res_conversion_rate_list = resdict['res_conversion_rate_list'][6:9]

    # 默认存数时是正确的
    fee_z, fee_y, fee_w, fee_p = promo_pred_ref_data[['fee_z', 'fee_y', 'fee_w', 'fee_p']].iloc[0].tolist()
    base_fee_list = [promo_base_dur, fee_z, fee_y, fee_w, fee_p] #fee_x可能为0
    pred_fee_list = [pred_dur, est_fee_z, est_fee_y, est_fee_w, est_fee_p] ##est_fee_x也可能为0

    # 需要先根据预计费用的输入判断是否能进行推广AIPL预测
    # 若某est_fee不为0，则说明需要预测：该细分推广渠道作为付费曝光中的一部分能达到的效果以及用其补齐缺口需要的额外费用。但该预测依赖起码其avg_cost_list和exppop_ratio_of_paid_list是有历史值的
    # 但若其fee为0，那么无历史值基础用于预测，那么预测应该无法进行，因为对于准备花一定费用的细分推广渠道无法预测其能达成的效果，那么也就无法预测付费曝光汇总人群量级以及一系列后续的指标。
    if (est_fee_z !=0 and fee_z==0) or (est_fee_y !=0 and fee_y==0) or (est_fee_w !=0 and fee_w==0) or (est_fee_p !=0 and fee_p==0):
        raise ValueError("参考期的推广细分渠道的历史人群曝光数据不足以支撑预测期推广细分渠道的效果和缺口预测")

    def get_list_by_colname(df, colname):
        return df[[col for col in df.columns if colname in col]].iloc[0].tolist()
    pop_list = get_list_by_colname(promo_pred_ref_data, '_pop_')
    z_exppop_list = get_list_by_colname(promo_pred_ref_data, '_z_exppop_')
    y_exppop_list = get_list_by_colname(promo_pred_ref_data, '_y_exppop_')
    w_exppop_list = get_list_by_colname(promo_pred_ref_data, '_w_exppop_')
    p_exppop_list = get_list_by_colname(promo_pred_ref_data, '_p_exppop_') #x_exppop可能为0
    paid_exppop_list = get_list_by_colname(promo_pred_ref_data, '_paid_exppop_')

    paid_exppop_prop_list = [a / b for a, b in zip(paid_exppop_list, pop_list)]
    est_paid_exppop_prop_list = [a * b for a,b in zip(paid_exppop_prop_list,paid_exppop_prop_multiple)]

    def calculate_lists(fee, exppop_list, est_fee, avg_cost_multiple, paid_exppop_list):
        #不管对不对某渠道进行预估（即去掉if est_fee==0），都对该渠道的历史数据进行计算（前提是该渠道fee!=0即投放了）
        if fee!=0:
            avg_cost_list = [fee / x for x in exppop_list]
            est_exppop_list = [est_fee / a / b for a,b in zip(avg_cost_list,avg_cost_multiple)]
        else:
            avg_cost_list = ['' for _ in range(len(exppop_list))]
            # est_exppop_list = ['' for _ in range(len(exppop_list))]
            est_exppop_list = [0 for _ in range(len(exppop_list))] #即使某历史渠道的est_fee不为0，但是因为fee为0，故设其est_exppop为0，不是无曝光人群的意思，而是无法预测其曝光人群，但为了不影响后续计算，故设为0
        exppop_ratio_of_paid_list = [a / b for a, b in zip(exppop_list, paid_exppop_list)]
        return avg_cost_list, est_exppop_list, exppop_ratio_of_paid_list

    z_avg_cost_list, est_z_exppop_list, z_exppop_ratio_of_paid_list = calculate_lists(fee_z, z_exppop_list, est_fee_z,
                                                                                      avg_cost_multiple,
                                                                                      paid_exppop_list)
    y_avg_cost_list, est_y_exppop_list, y_exppop_ratio_of_paid_list = calculate_lists(fee_y, y_exppop_list, est_fee_y,
                                                                                      avg_cost_multiple,
                                                                                      paid_exppop_list)
    w_avg_cost_list, est_w_exppop_list, w_exppop_ratio_of_paid_list = calculate_lists(fee_w, w_exppop_list, est_fee_w,
                                                                                      avg_cost_multiple,
                                                                                      paid_exppop_list)
    p_avg_cost_list, est_p_exppop_list, p_exppop_ratio_of_paid_list = calculate_lists(fee_p, p_exppop_list, est_fee_p,
                                                                                      avg_cost_multiple,
                                                                                      paid_exppop_list)

    paid_exppop_sum_list = [sum(values) for values in
                            zip(z_exppop_list, y_exppop_list, w_exppop_list, p_exppop_list)]
    paid_exppop_overlap_list = [a / b for a, b in zip(paid_exppop_list, paid_exppop_sum_list)] #重合度=直接汇总的除以相加后汇总的
    est_paid_exppop_sum_list = [sum(values) for values in
                                zip(est_z_exppop_list, est_y_exppop_list, est_w_exppop_list, est_p_exppop_list)]
    est_paid_exppop_dupsum_list = [a * b * c for a, b,c in
                                   zip(est_paid_exppop_sum_list, paid_exppop_overlap_list,overlap_multiple)]
    est_pop_list = [a / b for a, b in zip(est_paid_exppop_dupsum_list, est_paid_exppop_prop_list)]

    if ins=='蓄水人群预估':
        est_end_pop_list = [a + b for a, b in zip(sto_first_day_list, est_pop_list)]
        est_free_exppop_list = [a - b for a, b in zip(est_pop_list, est_paid_exppop_dupsum_list)]
    if ins=='新增人群预估':
        est_end_pop_list = est_pop_list

    est_paid_exppop_list = [a * b for a, b in zip(est_end_pop_list, est_paid_exppop_prop_list)]
    res_pop_paid_exppop_list = [a * b for a, b in zip(res_pop_list, est_paid_exppop_prop_list)]
    res_pop_paid_more_exppop_list = [a - b for a, b in zip(res_pop_paid_exppop_list, est_paid_exppop_list)]
    est_pop_gap_list = [a - b for a, b in zip(res_pop_list, est_end_pop_list)]
    est_end_purpop_list = [a * b for a, b in zip(est_end_pop_list, res_conversion_rate_list)]
    est_purpop_gap_list = [a * b for a, b in zip(est_pop_gap_list, res_conversion_rate_list)]
    est_end_trans_amount_list = [x * est_avg_order_price for x in est_end_purpop_list]
    est_trans_amount_gap_list = [x * est_avg_order_price for x in est_purpop_gap_list]
    if ins=='蓄水人群预估':
        temp_max = []
        temp_avg = []
        def calculate_lists(fee, exppop_list, est_pop_gap_list, est_paid_exppop_prop_list, exppop_ratio_of_paid_list,
                            avg_cost_list, est_fee,temp_max,temp_avg):
            if fee != 0:
                est_suppl_fee_list = [a * b * c * d for a, b, c, d in zip(est_pop_gap_list, est_paid_exppop_prop_list, exppop_ratio_of_paid_list,avg_cost_list)]
                x1 = max(est_suppl_fee_list[:5]) #因为历史PL无互动不参与费用计算
                x2 = sum(est_suppl_fee_list[:5]) / len(est_suppl_fee_list[:5])
                x3 = x1 / est_fee
                x4 = x2 / est_fee
                temp_max.append(x3)
                temp_avg.append(x4)
                x5 = x1 + est_fee
                x6 = x2 + est_fee
                est_suppl_fee_summ_list = [x1, x2, x3, x4, x5, x6]
            else:
                est_suppl_fee_list = ['' for _ in range(len(exppop_list))]
                est_suppl_fee_summ_list = ['', '', '', '','','']
            return est_suppl_fee_list, est_suppl_fee_summ_list,temp_max,temp_avg

        est_z_suppl_fee_list, est_z_suppl_fee_summ_list,temp_max,temp_avg = calculate_lists(fee_z, z_exppop_list, est_pop_gap_list,
                                                                          est_paid_exppop_prop_list,
                                                                          z_exppop_ratio_of_paid_list, z_avg_cost_list,
                                                                          est_fee_z,temp_max,temp_avg)
        est_y_suppl_fee_list, est_y_suppl_fee_summ_list,temp_max,temp_avg = calculate_lists(fee_y, y_exppop_list, est_pop_gap_list,
                                                                          est_paid_exppop_prop_list,
                                                                          y_exppop_ratio_of_paid_list, y_avg_cost_list,
                                                                          est_fee_y,temp_max,temp_avg)
        est_w_suppl_fee_list, est_w_suppl_fee_summ_list,temp_max,temp_avg = calculate_lists(fee_w, w_exppop_list, est_pop_gap_list,
                                                                          est_paid_exppop_prop_list,
                                                                          w_exppop_ratio_of_paid_list, w_avg_cost_list,
                                                                          est_fee_w,temp_max,temp_avg)
        est_p_suppl_fee_list, est_p_suppl_fee_summ_list,temp_max,temp_avg = calculate_lists(fee_p, p_exppop_list, est_pop_gap_list,
                                                                          est_paid_exppop_prop_list,
                                                                          p_exppop_ratio_of_paid_list, p_avg_cost_list,
                                                                          est_fee_p,temp_max,temp_avg)
        temp_max_value = sum(temp_max) / len(temp_max)
        temp_avg_value = sum(temp_avg) / len(temp_avg)

        def calculate_lists(fee, est_suppl_fee_summ_list, temp_max_value, temp_avg_value, est_fee):
            if fee == 0: #没有对应推广渠道的历史数据，但若有est_fee则仍然得到est_suppl_fee_summ相关值   #增加对历史和预测数据中细分推广渠道是否符合的判断后，fee==0时est_fee一定等于0了意义不大，但是该方法会在执行中被调用
                x3 = temp_max_value
                x4 = temp_avg_value
                x1 = x3 * est_fee
                x2 = x4 * est_fee
                x5 = x1 + est_fee
                x6 = x2 + est_fee
                est_suppl_fee_summ_list = [x1, x2, x3, x4, x5, x6]
            return est_suppl_fee_summ_list
        if est_z_suppl_fee_summ_list==['', '', '', '','','']:
            est_z_suppl_fee_summ_list = calculate_lists(fee_z, est_z_suppl_fee_summ_list, temp_max_value, temp_avg_value,
                                                    est_fee_z)
        if est_y_suppl_fee_summ_list==['', '', '', '','','']:
            est_y_suppl_fee_summ_list = calculate_lists(fee_y, est_y_suppl_fee_summ_list, temp_max_value, temp_avg_value,
                                                    est_fee_y)
        if est_w_suppl_fee_summ_list==['', '', '', '','','']:
            est_w_suppl_fee_summ_list = calculate_lists(fee_w, est_w_suppl_fee_summ_list, temp_max_value, temp_avg_value,
                                                    est_fee_w)
        if est_p_suppl_fee_summ_list==['', '', '', '','','']:
            est_p_suppl_fee_summ_list = calculate_lists(fee_p, est_p_suppl_fee_summ_list, temp_max_value, temp_avg_value,
                                                    est_fee_p)
        est_sum_suppl_fee_summ_list = [sum(values) for values in
                                       zip(est_z_suppl_fee_summ_list, est_y_suppl_fee_summ_list, est_w_suppl_fee_summ_list,
                                           est_p_suppl_fee_summ_list)]

    # display(在display之前对数据保留位数的处理，不在前面步骤中处理是因为这样才能计算地更准确) ##现改成了直接呈现原始数据，直接在template中限定格式即可达到想要的效果
    workbook = openpyxl.load_workbook(templateloc)  # 打开Excel模板
    worksheet = workbook['promores']  # 选择工作表
    # base_fee_list=floatwithtwo(base_fee_list)
    # pred_fee_list=floatwithtwo(pred_fee_list)
    res_list = [base_fee_list, pred_fee_list]
    if ins=='蓄水人群预估':
        # paid_exppop_prop_list=percentwithtwo(paid_exppop_prop_list)
        # z_exppop_ratio_of_paid_list=percentwithtwo(z_exppop_ratio_of_paid_list)
        # y_exppop_ratio_of_paid_list=percentwithtwo(y_exppop_ratio_of_paid_list)
        # w_exppop_ratio_of_paid_list=percentwithtwo(w_exppop_ratio_of_paid_list)
        # p_exppop_ratio_of_paid_list=percentwithtwo(p_exppop_ratio_of_paid_list)
        # paid_exppop_overlap_list=percentwithtwo(paid_exppop_overlap_list)
        # z_avg_cost_list=floatwithtwo(z_avg_cost_list)
        # y_avg_cost_list=floatwithtwo(y_avg_cost_list)
        # w_avg_cost_list=floatwithtwo(w_avg_cost_list)
        # p_avg_cost_list=floatwithtwo(p_avg_cost_list)
        # est_z_suppl_fee_summ_list=floatwithtwo(est_z_suppl_fee_summ_list)
        # est_y_suppl_fee_summ_list=floatwithtwo(est_y_suppl_fee_summ_list)
        # est_w_suppl_fee_summ_list=floatwithtwo(est_w_suppl_fee_summ_list)
        # est_p_suppl_fee_summ_list=floatwithtwo(est_p_suppl_fee_summ_list)
        # est_sum_suppl_fee_summ_list=floatwithtwo(est_sum_suppl_fee_summ_list)
        res_list1 = [pop_list, z_exppop_list, y_exppop_list, w_exppop_list, p_exppop_list, paid_exppop_list,
                     paid_exppop_prop_list, z_exppop_ratio_of_paid_list, y_exppop_ratio_of_paid_list,
                     w_exppop_ratio_of_paid_list, p_exppop_ratio_of_paid_list, paid_exppop_overlap_list,
                     z_avg_cost_list,y_avg_cost_list, w_avg_cost_list, p_avg_cost_list,
                     est_z_suppl_fee_summ_list, est_y_suppl_fee_summ_list, est_w_suppl_fee_summ_list,est_p_suppl_fee_summ_list,
                     est_sum_suppl_fee_summ_list]
        # est_z_exppop_list=toint(est_z_exppop_list)
        # est_y_exppop_list=toint(est_y_exppop_list)
        # est_w_exppop_list=toint(est_w_exppop_list)
        # est_p_exppop_list=toint(est_p_exppop_list)
        # est_paid_exppop_sum_list=toint(est_paid_exppop_sum_list)
        # est_paid_exppop_dupsum_list=toint(est_paid_exppop_dupsum_list)
        # est_paid_exppop_prop_list=percentwithtwo(est_paid_exppop_prop_list)
        # est_pop_list=toint(est_pop_list)
        # est_free_exppop_list=toint(est_free_exppop_list)
        # est_end_pop_list=toint(est_end_pop_list)
        # res_pop_list=toint(res_pop_list)
        # res_pop_paid_exppop_list=toint(res_pop_paid_exppop_list)
        # est_paid_exppop_list=toint(est_paid_exppop_list)
        # res_pop_paid_more_exppop_list=toint(res_pop_paid_more_exppop_list)
        # est_pop_gap_list=toint(est_pop_gap_list)
        # est_end_purpop_list=toint(est_end_purpop_list)
        # est_purpop_gap_list=toint(est_purpop_gap_list)
        # est_end_trans_amount_list=floatwithtwo(est_end_trans_amount_list)
        # est_trans_amount_gap_list=floatwithtwo(est_trans_amount_gap_list)
        # est_z_suppl_fee_list=floatwithtwo(est_z_suppl_fee_list)
        # est_y_suppl_fee_list=floatwithtwo(est_y_suppl_fee_list)
        # est_w_suppl_fee_list=floatwithtwo(est_w_suppl_fee_list)
        # est_p_suppl_fee_list=floatwithtwo(est_p_suppl_fee_list)
        res_list2 = [est_z_exppop_list, est_y_exppop_list, est_w_exppop_list, est_p_exppop_list,
                     est_paid_exppop_sum_list,
                     est_paid_exppop_dupsum_list, est_paid_exppop_prop_list, est_pop_list,
                     est_free_exppop_list, est_end_pop_list, res_pop_list, res_pop_paid_exppop_list,
                     est_paid_exppop_list,
                     res_pop_paid_more_exppop_list, est_pop_gap_list, est_end_purpop_list,
                     est_purpop_gap_list, est_end_trans_amount_list, est_trans_amount_gap_list, est_z_suppl_fee_list,
                     est_y_suppl_fee_list, est_w_suppl_fee_list, est_p_suppl_fee_list]
        # 这些list填充前仅保留前5个元素
        for m, n in zip(list(range(15, 31)) + list(range(58, 63)), res_list1):
            for i in list(range(0, 6)):
                cell = ['B', 'C', 'D', 'E', 'F', 'G'][i] + str(m)
                worksheet[cell] = n[i]
        for m, n in zip(list(range(31, 45)) + list(range(46, 51)) + list(range(52, 56)), res_list2):
            for i in list(range(0, 5)):
                cell = ['B', 'C', 'D', 'E', 'F'][i] + str(m)
                worksheet[cell] = n[i]
        for m, n in zip(['B', 'C'], res_list):
            for i in list(range(6, 11)):
                cell = m + str(i)
                worksheet[cell] = n[i-6]

    if ins=='新增人群预估':
        # paid_exppop_prop_list =percentwithtwo(paid_exppop_prop_list)
        # z_exppop_ratio_of_paid_list =percentwithtwo(z_exppop_ratio_of_paid_list)
        # y_exppop_ratio_of_paid_list =percentwithtwo(y_exppop_ratio_of_paid_list)
        # w_exppop_ratio_of_paid_list =percentwithtwo(w_exppop_ratio_of_paid_list)
        # p_exppop_ratio_of_paid_list =percentwithtwo(p_exppop_ratio_of_paid_list)
        # paid_exppop_overlap_list =percentwithtwo(paid_exppop_overlap_list)
        # z_avg_cost_list =floatwithtwo(z_avg_cost_list)
        # y_avg_cost_list =floatwithtwo(y_avg_cost_list)
        # w_avg_cost_list =floatwithtwo(w_avg_cost_list)
        # p_avg_cost_list =floatwithtwo(p_avg_cost_list)
        # est_z_exppop_list =toint(est_z_exppop_list)
        # est_y_exppop_list =toint(est_y_exppop_list)
        # est_w_exppop_list =toint(est_w_exppop_list)
        # est_p_exppop_list =toint(est_p_exppop_list)
        # est_paid_exppop_sum_list =toint(est_paid_exppop_sum_list)
        # est_paid_exppop_dupsum_list =toint(est_paid_exppop_dupsum_list)
        # est_paid_exppop_prop_list =percentwithtwo(est_paid_exppop_prop_list)
        # est_end_pop_list =toint(est_end_pop_list)
        # res_pop_list =toint(res_pop_list)
        # res_pop_paid_exppop_list =toint(res_pop_paid_exppop_list)
        # est_paid_exppop_list =toint(est_paid_exppop_list)
        # res_pop_paid_more_exppop_list =toint(res_pop_paid_more_exppop_list)
        # est_pop_gap_list =toint(est_pop_gap_list)
        # est_end_purpop_list =toint(est_end_purpop_list)
        # est_purpop_gap_list =toint(est_purpop_gap_list)
        # est_end_trans_amount_list =floatwithtwo(est_end_trans_amount_list)
        # est_trans_amount_gap_list =floatwithtwo(est_trans_amount_gap_list)

        res_list1 = [pop_list,z_exppop_list,y_exppop_list,w_exppop_list,p_exppop_list,paid_exppop_list,paid_exppop_prop_list,
                     z_exppop_ratio_of_paid_list,y_exppop_ratio_of_paid_list,w_exppop_ratio_of_paid_list,p_exppop_ratio_of_paid_list,
                     paid_exppop_overlap_list,z_avg_cost_list,y_avg_cost_list,w_avg_cost_list,p_avg_cost_list,est_z_exppop_list,est_y_exppop_list,
                     est_w_exppop_list,est_p_exppop_list,est_paid_exppop_sum_list,est_paid_exppop_dupsum_list,est_paid_exppop_prop_list,est_end_pop_list,
                     res_pop_list,res_pop_paid_exppop_list,est_paid_exppop_list,res_pop_paid_more_exppop_list,
                     est_pop_gap_list,est_end_purpop_list,est_purpop_gap_list,est_end_trans_amount_list,est_trans_amount_gap_list]
        # 这些list填充前仅保留前5个元素
        for m, n in zip(list(range(76, 104))+list(range(105, 110)), res_list1):
            for i in list(range(0, 3)):
                cell = ['B', 'C', 'D'][i] + str(m)
                worksheet[cell] = n[i]
        for m, n in zip(['B', 'C'], res_list):
            for i in list(range(68, 73)):
                cell = m + str(i)
                worksheet[cell] = n[i-68]
    workbook.save(res_file_path)    #预测结果存储路径


'''—————————————————————————————方法：该功能完整方法—————————————————————————————'''
def pop_predict(dffront, pop_res_path, promp_res_path):
    """生成人群预测文件，并存储到指定的位置
        dffront：预测参数
        pop_res_path：指定pop_pred_res的结果文件的存储路径
        promp_res_path：指定promp_pred_res的结果文件的存储路径
    """
    pre_predict_check_returndict = pre_predict_check(dffront)
    get_pop_pred_res_dict = get_pop_pred_res(pre_predict_check_returndict, pop_predict_temp_xml, pop_res_path)
    get_promo_pred_res(pre_predict_check_returndict, get_pop_pred_res_dict, '蓄水人群预估', promo_predict_temp_xml,
                       promp_res_path)
    get_promo_pred_res(pre_predict_check_returndict, get_pop_pred_res_dict, '新增人群预估', promp_res_path,
                       promp_res_path)


####################################################### 前端调用加工方法 ########################################################
'''—————————————————————————————功能：其他预测-人群预测—————————————————————————————'''
##预测说明：
#假设月预测的是2023-06，蓄水期就为2023-05，在蓄水期第二天做该月预测，0502爬0501的数据，4月数据为蓄水期推广参考，2022-06为活动期推广参考，2022-05为蓄水期人群参考，2022-06为活动期人群参考
#同样地在大促蓄水期结束的第二天做该大促预测

## 选择预测大促时需要传入的参数
dffront = pd.DataFrame(
    {'est_trans_amount_goal': [32000000], 'est_avg_order_price': [74], 'benchmark_multiple': [0.7],
     'predict_obj': ['大促'], 'predict_big_promo_year': ['2023'], 'predict_big_promo_name': ['双十一'],'predict_month':[np.nan], #选择大促时不会出现predict_month输入项，故为nan
     'ref_big_promo_year': [''], 'ref_big_promo_name': [''],
     'act_pop_pred_ref_month': [''],
     'sto_promo_pred_ref_month': [''], 'act_promo_pred_ref_month': [''],#为''表示无输入值，程序在处理时会取默认值（仅ref_big_promo_year、ref_big_promo_name、act_pop_pred_ref_month、sto_promo_pred_ref_month、act_promo_pred_ref_month可以输入值取''）
     'sto_est_fee_z': [980003], 'sto_est_fee_y': [0], 'sto_est_fee_w': [988575], 'sto_est_fee_p': [100000],
     'act_est_fee_z': [770000], 'act_est_fee_y': [190000], 'act_est_fee_w': [0], 'act_est_fee_p': [0],#取0值表示该渠道不投
     'avg_cost_multiple_sto': [[1.05,1.05,1.05,1.05,1.05,1.05]], 'avg_cost_multiple_act': [[1.1,1.1,1]],
     'overlap_multiple_sto': [[0.95,0.95,0.95,0.95,0.95,0.95]], 'overlap_multiple_act': [[1,1,1]],
     'paid_exppop_prop_multiple_sto': [[1,2.5,1,2.5,1.1,1]], 'paid_exppop_prop_multiple_act': [[1,1,1]]
     })  # act_pop_pred_ref_month定下来了，sto_pop_pred_ref_month就定下来了

# # 选择预测月时需要传入的参数
# dffront = pd.DataFrame(
#     {'est_trans_amount_goal': [2800000], 'est_avg_order_price': [75], 'benchmark_multiple': [0.7],
#      'predict_obj':['月'],'predict_big_promo_year': [np.nan], 'predict_big_promo_name': [np.nan],'predict_month':['2023-12'], #选择月时不会出现predict_big_promo_year、predict_big_promo_name输入项，故为nan
#      'ref_big_promo_year': [np.nan], 'ref_big_promo_name': [np.nan],#选择月时不会出现ref_big_promo_year、ref_big_promo_name输入项，故为nan
#      'act_pop_pred_ref_month':[''],
#      'sto_promo_pred_ref_month':[''],'act_promo_pred_ref_month':[''],#为''表示无输入值，程序在处理时会取默认值（仅act_pop_pred_ref_month、sto_promo_pred_ref_month、act_promo_pred_ref_month可以输入值取''）
#      'sto_est_fee_z': [200000], 'sto_est_fee_y': [46626829], 'sto_est_fee_w': [46648279],'sto_est_fee_p': [324846297],
#      'act_est_fee_z': [200000], 'act_est_fee_y': [46626829], 'act_est_fee_w': [46648279],'act_est_fee_p': [324846297],#取0值表示该渠道不投
#      'avg_cost_multiple_sto': [[1.05,1.05,1.05,1.05,1.05,1.05]], 'avg_cost_multiple_act': [[1.1,1.1,1]],
#      'overlap_multiple_sto': [[0.95,0.95,0.95,0.95,0.95,0.95]], 'overlap_multiple_act': [[1,1,1]],
#      'paid_exppop_prop_multiple_sto': [[1,2.5,1,2.5,1.1,1]], 'paid_exppop_prop_multiple_act': [[1,1,1]]
#      }) #act_pop_pred_ref_month定下来了，sto_pop_pred_ref_month就定下来了

pop_predict(dffront)








# # ###向库表中插入数据测试
# def allupdate_insert(df: pd.DataFrame, class_name):
#     df.replace(np.nan, None, inplace=True)
#     session = get_session()
#     df = df.to_dict(orient='records')
#     session.query(class_name).delete()
#     objects = [class_name(**eachline) for eachline in df]
#     session.add_all(objects)
#     print('开始commit')
#     session.commit()
#     session.close()
# upload_df=pd.read_excel( 'D:/sibaoproject_文档/各种表以及任务分配10.22/电商模型预测部分/上传测试数据.xlsx' , sheet_name=None)
# # upload_df1=upload_df['大促区间表']
# # allupdate_insert(upload_df1,big_promotion_duration)
# # upload_df2=upload_df['人群购买人群曝光人群月表']
# # upload_df2 = upload_df2.set_index('列名').transpose()
# # allupdate_insert(upload_df2,pop_purpop_exppop_month)
# upload_df3=upload_df['人群购买人群曝光人群大促表']
# upload_df3 = upload_df3.set_index('列名').transpose()
# allupdate_insert(upload_df3,pop_purpop_exppop_bigpromo)
